/********************************************/
  // Creation History
  // --------------------------------------------------------------
  //	Company:			HTHK
  //	NAME:				StaffReportSearch.java
  //    Title   :			Select employ data to 
  //				come into being report
  //	FUNCTION:
  //	BY:				Poly Li
  //	DATE:				17/12/2003
  //	update_date:			15-01-2004
  //	update_by:			Poly Li
  // --------------------------------------------------------------
/*********************************************/

package tss.staffreport;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;

import tss.common.DBServlet2;
import tss.jobreport.ReportContainer;

import com.ffpc.sql.DataException;
import com.ffpc.struts.StrutsMessage;
import com.util.JdbcUtil;


public class StaffReportSearch extends DBServlet2 
{
	private Logger logger = Logger.getLogger(StaffReportSearch.class);
	@Override
	public String perform (HttpServletRequest request,HttpServletResponse res,StrutsMessage message)
		throws ServletException,IOException,SQLException,DataException,Exception
	{       
                HttpSession session = request.getSession();
		Connection con = this.getConnection();
		
		String projectName = null;
		if(request.getParameter("project_name") != null)
			projectName = request.getParameter("project_name");
		String staffNo = null;
		if(request.getParameter("staff_name") != null)
			staffNo = request.getParameter("staff_name");
        //	String yearFrom = req.getParameter("year_from");
        //	String monthFrom = req.getParameter("month_from");
        //	String yearTo = req.getParameter("year_to");
        //	String monthTo = req.getParameter("month_to");


		session.setAttribute("project_name",projectName);
		session.setAttribute("staff_name",staffNo);
	//	session.setAttribute("yearFrom",yearFrom);
	//	session.setAttribute("monthFrom",monthFrom);
	//	session.setAttribute("yearTo",yearTo);
	//	session.setAttribute("monthTo",monthTo);

	        String p_fromYear,p_fromMonth,p_toYear,p_toMonth;
		String p_fromDay,p_toDay;
				String search_date = request.getParameter("selected_date");
		String search_date1 = request.getParameter("selected_date1");
		session.setAttribute("date_from",search_date);
		session.setAttribute("date_to",search_date1);

                p_fromYear = search_date.substring(0,4);
                p_toYear = search_date1.substring(0,4);
                p_fromMonth = search_date.substring(5,7);
                p_toMonth = search_date1.substring(5,7);
                p_fromDay = search_date.substring(8,10);
                p_toDay = search_date1.substring(8,10);

		session.setAttribute("year_from",p_fromYear);
		session.setAttribute("month_from",p_fromMonth);
		session.setAttribute("year_to",p_toYear);
		session.setAttribute("month_to",p_toMonth);
                session.setAttribute("day_from",p_fromDay);
                session.setAttribute("day_to",p_toDay);
		
        	
        	Vector reportDate = new Vector();    //str_year_month
        	Vector staffReportResult = new Vector();

	        int yearDifference = 0;
	        int monthDifference = 0;
	        int year_to = Integer.parseInt(p_toYear);
	        int year_from = Integer.parseInt(p_fromYear);
	        int month_to  = Integer.parseInt(p_toMonth);
	        int month_from = Integer.parseInt(p_fromMonth);
                yearDifference = year_to - year_from;
                monthDifference = yearDifference*12+(month_to - month_from)+1;
                
                int[] month = new int[monthDifference];
                int[] year = new int[monthDifference];
                month[0] = month_from;
                year[0] = year_from;
                for(int a = 1;a<monthDifference;a++)
                  {
                  	month[a] = month[a-1]+1;
                  	if(month[a-1] == 12)
                  	{
                  		month[a] = 1;
                  		year[a] = year[a-1]+1;
                  	}
                  	else
                  		year[a] = year[a-1];
                 
	            }
	          String Date1[] = new String[monthDifference];
	          String Date2[] = new String[monthDifference];
                  for(int s=0;s<monthDifference;s++)
                  {
                  	String monthFormat = "";
                        if(month[s]<10)
                        {
                        	monthFormat = "0"+(new Integer(month[s])).toString();
                        }
                        else
                        {
                        	monthFormat = (new Integer(month[s])).toString();
                        }

                  	Date1[s] = (new Integer(year[s])).toString() + "-" + monthFormat;
                  	Date2[s] = (new Integer(year[s])).toString() + "/" + monthFormat;
                   }
		

		for(int count = 0;count < monthDifference;count++)
        	{
			ReportContainer staffReportList = new ReportContainer();


			String ls_Sql = "select a.staff_no, b.surname||' '||b.first_name, a.project_name, sum(a.spend)";
				ls_Sql += " from tss_v_project_report a, tss.tss_c_employee b";
				ls_Sql += " where a.staff_no=b.staff_no";
	                        ls_Sql += " AND TO_CHAR(A.SPEND_DATE, 'YYYY/MM/DD') >= '"+p_fromYear+"/"+p_fromMonth+"' ";
	                        ls_Sql += " AND TO_CHAR(A.SPEND_DATE, 'YYYY/MM') <= '"+p_toYear+"/"+p_toMonth+"'";
				ls_Sql += " and to_char(a.spend_date, 'yyyy-mm')='"+Date1[count]+"'";
			        if(count==0)
                                {
                        	            ls_Sql += " and to_char(a.spend_date, 'dd')>='"+p_fromDay+"' ";
                                }
                                if(count == monthDifference-1)
                                {
                        	            ls_Sql += " and to_char(a.spend_date, 'dd')<='"+p_toDay+"' ";
                                }
			        if (staffNo != null && staffNo.length() > 0)
				            ls_Sql += " and a.staff_no='" + staffNo + "'";
			        if ( projectName != null && projectName.length() > 0 )
				            ls_Sql += " and a.project_name='" + projectName + "'";

				ls_Sql += " group by a.staff_no, a.project_name, b.surname||' '||b.first_name";
				ls_Sql += " having sum(a.spend) > 0";
				ls_Sql += " order by a.staff_no, a.project_name";
			//logger.debug("StaffReportSearch.java--  SQL is : " + ls_Sql);
			PreparedStatement staffStmt = con.prepareStatement(ls_Sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = null;
	  		//ReportContainer staffReportList = new ReportContainer();

			float spendTotal = 0f;
			try
			{
				Vector staffReport = new Vector();

				rs = staffStmt.executeQuery();
				//rs = stmt.executeQuery(ls_Sql);
				
				while(rs.next())
				{
					spendTotal += rs.getFloat(4);
					
					String staff_report[] = new String[4];
					staff_report[0] = rs.getString(1);//staff NO
		                 	staff_report[1] = rs.getString(2);//staff Name
		                 	staff_report[2] = rs.getString(3);//Project Name
		                 	staff_report[3] = rs.getString(4);//Spend
					
					staffReport.addElement(staff_report);
				}
				
				if(!staffReport.isEmpty())
				{	
					String spend_total[] = new String[4];
					spend_total[0]= "<b>Total</b>";
					spend_total[1]= "&nbsp;";
					spend_total[2]= "&nbsp;";
					spend_total[3]= ""+spendTotal;
		                 	
		                 	staffReport.addElement(spend_total);
		                 	
		                 	String report_date[] = new String[3];
		                 	report_date[0] = Date2[count];
			                if(count == 0)
			                {
				                      report_date[1] = p_fromDay;
			                }
			                else
			                {
			                              report_date[1] = "01";
			                }
			                if(count == monthDifference-1)
			                {
				                        report_date[2] = p_toDay;
			                }
			                else
			                {
				                        int yearInt = Integer.parseInt(Date2[count].substring(0,4));
				                        int monthInt = Integer.parseInt(Date2[count].substring(5,7));
				                        if (monthInt==4 || monthInt==6 || monthInt==9 || monthInt==11)
				                        {
						                        report_date[2] = "30";
				                        }
				                        else if(monthInt == 2)
				                        {
						                         if(yearInt%4==0 && (yearInt%100!=0 || yearInt%400==0))
							                        report_date[2] = "29";
						                         else
							                        report_date[2] = "28";
				                        }
				                        else
				                        {
				                                        report_date[2] = "31";
				                        }
			                }
		                 	reportDate.addElement(report_date);
		                 	logger.debug("reportDate ="+reportDate);
				}

				staffReportList.setProjects(staffReport);
				staffReportResult.addElement(staffReportList);

			}
			catch (Exception e)
			{
				logger.error(e, e);
			}finally{
				JdbcUtil.release(staffStmt, rs);
			}
		}
                
		if (reportDate.isEmpty())
		{
			message.setMessage("No record, please input another criteria.");
			return "tss/staffreport/StaffExport.jsp";
		}
		//find more than one records
		else
		{
			request.setAttribute("staffReportResult",staffReportResult);
			request.setAttribute("reportDate",reportDate);
			return "tss/staffreport/StaffReport.jsp";
		}
	}

}
